package com.abel.bigwater.wflow.mapper

import com.abel.bigmeter.service.DmaParam
import com.abel.bigmeter.service.LocParam
import com.abel.bigwater.model.BwDataStat
import com.abel.bigwater.model.BwLoss
import com.abel.bigwater.model.DataRange
import org.apache.ibatis.annotations.*

@Mapper
interface StatMapper {
    @SelectKey(statement = arrayOf("SELECT LAST_INSERT_ID() AS id"),
            resultType = Long::class, keyProperty = "id", keyColumn = "loss_id",
            before = false)
    @Insert("""<script>
        insert into bw_dma_loss (stat_date, duration_day, dma_id, meter_id,
        loss_r, parent_phyloss0, pressure_t, parent_phyloss1,
        parent_v0, num_seconds, phyloss_rate,
        number_meter, avg_meter_v, gross_meter_v,
        visual_loss, visual_loss_rate,
        loss_status, q_night, q_avg,
        firm_id, create_date, create_by,
        comments)

        values(#{statDate}, #{durationDay}, #{dmaId}, #{meterId},
        #{r}, #{parentPhyloss0}, #{pressureT}, #{parentPhyloss1},
        #{parentV0}, #{numSeconds}, #{phylossRate},
        #{numberMeter}, #{avgMeterv}, #{grossMeterv},
        #{visualLoss}, #{visualLossRate},
        #{status}, #{flowNight}, #{avgFlow},
        #{firmId}, #{createDate}, #{createBy},
        #{comments})
        </script>""")
    fun insertLoss(loss: BwLoss): Int

    @Delete("""<script>
        DELETE FROM bw_dma_loss
        <where>
            <if test="idList != null">
                AND loss_id IN
                <foreach collection="idList" item="lossId" open="(" separator="," close=")" index="idx">
                    #{lossId}
                </foreach>
            </if>
        </where>
        </script>
    """)
    fun deleteLoss(lp: LocParam): Int

    @Select("""
        <script>
        select s.loss_id as id,
        s.stat_date as statDate,
        s.duration_day as durationDay,
        s.dma_id as dmaId,
        s.meter_id as meterId,
        s.loss_r as r,
        d.dma_name AS dmaName,
        s.parent_phyloss0 as parentPhyloss0,
        s.pressure_t as pressureT,
        s.parent_phyloss1 as parentPhyloss1,
        s.parent_v0 as parentV0,
        s.num_seconds AS numSeconds,
        s.phyloss_rate as phylossRate,
        s.number_meter as numberMeter,
        s.avg_meter_v as avgMeterv,
        s.gross_meter_v as grossMeterv,
        s.visual_loss as visualLoss,
        s.visual_loss_rate as visualLossRate,
        s.loss_status as status,
        s.q_night AS flowNight,
        s.q_avg AS avgFlow,
        s.create_by AS createBy,
        s.create_date AS createDate,
        s.firm_id as firmId,
        s.comments AS comments
        from bw.bw_dma_loss s
        LEFT JOIN bw.bw_dma d ON (s.dma_id = d.dma_id)
        <where>
            <if test="statDate != null">
                s.stat_date = #{statDate}
            </if>
            <if test="statDate1 != null">
                AND s.stat_date &gt;= #{statDate1}
            </if>
            <if test="statDate2 != null">
                AND s.stat_date &lt; #{statDate2}
            </if>
            <if test="durationDay != null">
                AND s.duration_day = #{durationDay}
            </if>
            <if test="firmId != null">
                AND d.firm_id LIKE #{firmId}
            </if>
            <if test="dmaId != null">
                AND s.dma_id LIKE #{dmaId}
            </if>
            <if test="createDate1 != null">
                AND s.create_date &gt;= #{createDate1}
            </if>
            <if test="createDate2 != null">
                AND s.create_date &lt; #{createDate2}
            </if>
        </where>
        order by s.stat_date desc, s.dma_id, s.phyloss_rate desc
        limit 0, 1500
        </script>
    """)
    fun listLoss(lp: LocParam): List<BwLoss>

    @SelectKey(statement = arrayOf("SELECT LAST_INSERT_ID() AS id"), before = false,
            keyColumn = "stat_id", keyProperty = "id", resultType = Long::class)
    @Insert("""
        <script>
        insert into bw_data_stat (stat_date, dma_id, meter_id, stat_type_id, duration_hour,
        q_max, q_min, q_avg,
        stat_days, qmin_avg, qmin_xigma, exceed_ratio,
        gross_v, num_seconds,
        v_q0, v_q1, v_q2, v_q3, v_q4,
        firm_id,
        create_date, create_by)
        values (#{statDate}, #{dmaId}, #{meterId}, #{statTypeId}, #{durationHour},
        #{qmax}, #{qmin}, #{qavg},
        #{statDays}, #{qminAvg}, #{qminXigma}, #{exceedRatio},
        #{grossV}, #{numSeconds},
        #{q0Sum}, #{q1Sum}, #{q2Sum}, #{q3Sum}, #{q4Sum},
        #{firmId},
        #{createDate}, #{createBy})
        </script>
    """)
    fun insertStat(stat: BwDataStat): Int

    @Select("""
        <script>
        select
        s.stat_id as id,
        s.stat_date as statDate,
        s.dma_id as dmaId,
		d.dma_name AS dmaName,
        s.meter_id as meterId,
        s.stat_type_id as statTypeId,
        s.duration_hour as durationHour,
        s.q_max as qmax,
        s.q_min as qmin,
        s.q_avg as qavg,
        s.stat_days as statDays,
        s.qmin_avg as qminAvg,
        s.qmin_xigma as qminXigma,
        s.exceed_ratio as exceedRatio,
        s.gross_v as grossV,
        s.num_seconds AS numSeconds,
        s.v_q0 as q0Sum,
        s.v_q1 as q1Sum,
        s.v_q2 as q2Sum,
        s.v_q3 as q3Sum,
        s.v_q4 as q4Sum,
        s.create_by AS createBy,
        s.create_date AS createDate,
        s.firm_id as firmId
        FROM bw.bw_data_stat s
		JOIN bw.bw_dma d ON (s.dma_id = d.dma_id)
        <where>
            <if test="statDate != null">
                s.stat_date = #{statDate}
            </if>
            <if test="dmaId != null">
                and s.dma_id LIKE #{dmaId}
            </if>
            <if test="statTypeId != null">
                and s.stat_type_id = #{statTypeId}
            </if>
            <if test="statDate1 != null">
                and s.stat_date &gt;= #{statDate1}
            </if>
            <if test="statDate2 != null">
                and s.stat_date &lt; #{statDate2}
            </if>
            <if test="firmId != null">
                and d.firm_id LIKE #{firmId}
            </if>
            <if test="createDate1 != null">
                AND s.create_date &gt;= #{createDate1}
            </if>
            <if test="createDate2 != null">
                AND s.create_date &lt; #{createDate2}
            </if>
        </where>
        order by s.stat_date desc, s.dma_id
        limit 0, 1500

        </script>
    """)
    fun listStat(lp: LocParam): List<BwDataStat>

    @Delete("""
        <script>
        DELETE FROM bw_data_stat
        <where>
            <if test="idList != null">
                AND stat_id IN
                <foreach collection="idList" item="lossId" open="(" separator="," close=")" index="idx">
                    #{lossId}
                </foreach>
            </if>
        </where>
        </script>
    """)
    fun deleteStat(lp: LocParam): Int

    @Select("""
        <script>
        SELECT firm_id AS firmId,
        dma_id AS dmaId,
        min(stat_date) as minTime, max(stat_date) as maxTime
        from bw.bw_dma_loss
        <where>
            <if test="statDate1 != null">
                stat_date &gt;= #{statDate1}
            </if>
            <if test="statDate2 != null">
                stat_date &lt;= #{statDate2}
            </if>
            <if test="durationDay != null">
                and duration_day = #{durationDay}
            </if>
            <if test="firmId != null">
                AND firm_id LIKE #{firmId}
            </if>
            <if test="dmaId != null">
                and dma_id = #{dmaId}
            </if>
        </where>
        GROUP BY firm_id, dma_id
        </script>
    """)
    fun lossDataRange(lp: LocParam): List<DataRange>

    @Select("""
        <script>
        SELECT firm_id AS firmId,
        dma_id AS dmaId,
        min(stat_date) as minTime, max(stat_date) as maxTime
        from bw.bw_data_stat
        <where>
            <if test="statDate1 != null">
                stat_date &gt;= #{statDate1}
            </if>
            <if test="statDate2 != null">
                stat_date &lt;= #{statDate2}
            </if>
            <if test="durationDay != null">
                and duration_day = #{durationDay}
            </if>
            <if test="firmId != null">
                AND firm_id LIKE #{firmId}
            </if>
            <if test="dmaId != null">
                and dma_id = #{dmaId}
            </if>
        </where>
        GROUP BY firm_id, dma_id
        </script>
    """)
    fun statDataRange(lp: LocParam): List<DataRange>

    @Select("""<script>
        SELECT d.dma_id AS dmaId
        , d.dma_name AS dmaName
        , s.*
        FROM bw.bw_dma d
        LEFT JOIN (
        SELECT dma_id AS dmaId2
        , MAX(stat_date) AS statDate
        , SUM(parent_phyloss0) AS parentPhyloss0
        , SUM(parent_phyloss1) AS parentPhyloss1
        , SUM(parent_v0) AS parentV0
        , SUM(duration_day) as durationDay
        FROM bw.bw_dma_loss
        <where>
            <if test="dmaId != null and dmaId != ''">
                dma_id LIKE #{dmaId}
            </if>
            <if test="firmId != null and firmId != ''">
                AND firm_id LIKE #{firmId}
            </if>
            <if test="statDate1 != null">
                AND stat_date &gt;= #{statDate1}
            </if>
            <if test="statDate2 != null">
                AND stat_date &lt; #{statDate2}
            </if>
        </where>
        GROUP BY dma_id
        ) s ON (d.dma_id = s.dmaId2)
        <where>
            <if test="dmaId != null and dmaId != ''">
                dma_id LIKE #{dmaId}
            </if>
            <if test="firmId != null and firmId != ''">
                AND firm_id LIKE #{firmId}
            </if>

            <if test="1 == 1">
                AND (d.dma_status IS NULL OR d.dma_status = 'WORK')
            </if>
        </where>
        ORDER BY dmaId
    </script>""")
    fun sumLossList(dp: DmaParam): List<BwLoss>?

    @Select("""<script>
        select dma_id AS dmaId,
        min(stat_date) as minTime, max(stat_date) as maxTime
        from bw.bw_dma_loss
        <where>
            <if test="statDate != null">
                stat_date = #{statDate}
            </if>
            <if test="durationDay != null">
                and duration_day = #{durationDay}
            </if>
            <if test="firmId != null">
                AND firm_id LIKE #{firmId}
            </if>
            <if test="dmaId != null">
                and dma_id = #{dmaId}
            </if>
            <if test="meterId != null">
                and meter_id = #{meterId}
            </if>
        </where>
    </script>""")
    fun selectLossDateRange(lp: LocParam): List<BwLoss>?

    @Select("""<script>
        select s.loss_id as id,
        s.stat_date as statDate,
        s.duration_day as durationDay,
        s.dma_id as dmaId,
        s.meter_id as meterId,
        s.loss_r as r,
        d.dma_name AS dmaName,
        s.parent_phyloss0 as parentPhyloss0,
        s.pressure_t as pressureT,
        s.parent_phyloss1 as parentPhyloss1,
        s.parent_v0 as parentV0,
        s.num_seconds AS numSeconds,
        s.phyloss_rate as phylossRate,
        s.number_meter as numberMeter,
        s.avg_meter_v as avgMeterv,
        s.gross_meter_v as grossMeterv,
        s.visual_loss as visualLoss,
        s.visual_loss_rate as visualLossRate,
        s.loss_status as status,
        s.q_night AS qNight,
        s.q_avg AS qAvg,
        s.create_by AS createBy,
        s.create_date AS createDate,
        s.firm_id as firmId,
        s.comments AS comments
        from bw.bw_dma_loss s
        LEFT JOIN bw.bw_dma d ON (s.dma_id = d.dma_id)
        <where>
            <if test="statDate != null">
                s.stat_date = #{statDate}
            </if>
            <if test="statDate1 != null">
                AND s.stat_date &gt;= #{statDate1}
            </if>
            <if test="statDate2 != null">
                AND s.stat_date &lt; #{statDate2}
            </if>
            <if test="durationDay != null">
                AND s.duration_day = #{durationDay}
            </if>
            <if test="firmId != null">
                AND d.firm_id LIKE #{firmId}
            </if>
            <if test="dmaId != null">
                AND s.dma_id LIKE #{dmaId}
            </if>
            <if test="meterId != null">
                AND (s.meter_id LIKE #{meterId} OR s.meter_id IS NULL)
            </if>
            <if test="createDate1 != null">
                AND s.create_date &gt;= #{createDate1}
            </if>
            <if test="createDate2 != null">
                AND s.create_date &lt; #{createDate2}
            </if>

            <if test="1 == 1">
                AND (d.dma_status IS NULL OR d.dma_status = 'WORK')
            </if>
        </where>
        order by s.stat_date desc, s.dma_id, s.phyloss_rate desc
        limit 0, 1500
    </script>""")
    fun selectLoss(dp: DmaParam): List<BwLoss>?
}